package com.lyw.GeoLocationClient.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.lyw.GeoLocationClient.ui.GeotableItem;

import java.util.LinkedList;
import java.util.List;

import static com.lyw.GeoLocationClient.utils.LogUtils.LOGD;

public class GeotableDB {
	private GeotableDBHelper helper;

	public GeotableDB(Context context) {
		helper = new GeotableDBHelper(context);
	}

	public GeotableItem selectInfo(String geoID) {
		GeotableItem geotableItem = new GeotableItem();
		SQLiteDatabase db = helper.getReadableDatabase();

        if (db != null) {
            Cursor c = db.rawQuery("select * from " +
                            GeotableDBHelper.GEO_TABLE +
                            " where " +
                            GeotableDBHelper.GEO_ID +
                            "=?",
                    new String[] { geoID + "" });
            if (c != null && c.moveToFirst()) {
                geotableItem.setTitle(c.getString(c.getColumnIndex(GeotableDBHelper.GEO_TITLE)));
                geotableItem.setPolId(c.getString(c.getColumnIndex(GeotableDBHelper.POL_ID)));
                geotableItem.setPushID(c.getInt(c.getColumnIndex(GeotableDBHelper.PUSH_ID)));
                geotableItem.setPushChannel(c.getInt(c.getColumnIndex(GeotableDBHelper.PUSH_CHANNEL)));
                geotableItem.setName(c.getString(c.getColumnIndex(GeotableDBHelper.NAME)));
                geotableItem.setUnit(c.getString(c.getColumnIndex(GeotableDBHelper.UNIT)));
                geotableItem.setGroup(c.getString(c.getColumnIndex(GeotableDBHelper.GROUP)));
                geotableItem.setImei(c.getString(c.getColumnIndex(GeotableDBHelper.IMEI)));
                geotableItem.setTime(c.getString(c.getColumnIndex(GeotableDBHelper.TIME)));
            } else {
                return null;
            }
        } else {
            return null;
        }

		return geotableItem;
	}

	public void addGeotable(List<GeotableItem> list) {
		SQLiteDatabase db = helper.getWritableDatabase();
        if (db == null) return;

		for (GeotableItem u : list) {
            insertGeotable(db, u);
		}
		db.close();
	}

	public void addGeotable(GeotableItem u) {
        if (selectInfo(String.valueOf(u.getGeoId())) != null) {
            update(u);
            return;
        }
        SQLiteDatabase db = helper.getWritableDatabase();
        if (db != null) {
            insertGeotable(db, u);
            db.close();
        }

    }

    private void insertGeotable(SQLiteDatabase db, GeotableItem u) {
        LOG("insert table " + u.getTitle() + u.getGeoId());
        db.execSQL(
                "insert into " +
                        GeotableDBHelper.GEO_TABLE
                        + " ("
                        + GeotableDBHelper.GEO_ID + ","
                        + GeotableDBHelper.GEO_TITLE + ","
                        + GeotableDBHelper.POL_ID + ","
                        + GeotableDBHelper.PUSH_ID + ","
                        + GeotableDBHelper.PUSH_CHANNEL + ","
                        + GeotableDBHelper.NAME + ","
                        + GeotableDBHelper.UNIT + ","
                        + GeotableDBHelper.GROUP + ","
                        + GeotableDBHelper.IMEI + ","
                        + GeotableDBHelper.TIME
                        + " )"
                        + " values(?,?,?,?,?,?,?,?,?,?)",
                new Object[]{
                        u.getGeoId(), u.getTitle(), u.getPolId(), u.getPushID(), u.getPushChannel(),
                        u.getName(), u.getUnit(), u.getGroup(), u.getImei(), u.getTime()
                }
        );
    }


	public void updateGeotable(List<GeotableItem> list) {
		if (list.size() > 0) {
            delete();
            addGeotable(list);
        }
	}

    private GeotableItem fillGeotable(Cursor c) {
        if (c != null) {
            GeotableItem geotableItem = new GeotableItem();
            geotableItem.setGeoId(c.getInt(c.getColumnIndex(GeotableDBHelper.GEO_ID)));
            geotableItem.setTitle(c.getString(c.getColumnIndex(GeotableDBHelper.GEO_TITLE)));
            geotableItem.setPolId(c.getString(c.getColumnIndex(GeotableDBHelper.POL_ID)));
            geotableItem.setPushID(c.getInt(c.getColumnIndex(GeotableDBHelper.PUSH_ID)));
            geotableItem.setPushChannel(c.getInt(c.getColumnIndex(GeotableDBHelper.PUSH_CHANNEL)));
            geotableItem.setName(c.getString(c.getColumnIndex(GeotableDBHelper.NAME)));
            geotableItem.setUnit(c.getString(c.getColumnIndex(GeotableDBHelper.UNIT)));
            geotableItem.setGroup(c.getString(c.getColumnIndex(GeotableDBHelper.GROUP)));
            geotableItem.setImei(c.getString(c.getColumnIndex(GeotableDBHelper.IMEI)));
            geotableItem.setTime(c.getString(c.getColumnIndex(GeotableDBHelper.TIME)));
            return geotableItem;
        } else {
            return null;
        }
    }

	public List<GeotableItem> getTables() {
		SQLiteDatabase db = helper.getWritableDatabase();
		List<GeotableItem> list = new LinkedList<GeotableItem>();
        Cursor c = null;
        if (db != null) {
            c = db.rawQuery("select * from " +
                    GeotableDBHelper.GEO_TABLE, null);
            while (c.moveToNext()) {
                GeotableItem u = fillGeotable(c);
                list.add(u);
            }
            c.close();
            db.close();
        }

		return list;
	}

	public void update(GeotableItem u) {
		SQLiteDatabase db = helper.getWritableDatabase();
        if (db != null) {
            updateGeotable(db, u);
            db.close();
        }
	}

    private void updateGeotable(SQLiteDatabase db, GeotableItem u) {
        db.execSQL(
                "update " +
                        GeotableDBHelper.GEO_TABLE
                        + " set "
                        + GeotableDBHelper.GEO_ID + "=?,"
                        + GeotableDBHelper.GEO_TITLE + "=?,"
                        + GeotableDBHelper.POL_ID + "=?,"
                        + GeotableDBHelper.PUSH_ID + "=?,"
                        + GeotableDBHelper.PUSH_CHANNEL + "=?,"
                        + GeotableDBHelper.NAME + "=?,"
                        + GeotableDBHelper.UNIT + "=?,"
                        + GeotableDBHelper.GROUP + "=?,"
                        + GeotableDBHelper.IMEI + "=?,"
                        + GeotableDBHelper.TIME + "=? "
                        + " where " + GeotableDBHelper.GEO_ID + "=?"
                ,
                new Object[]{
                        u.getGeoId(), u.getTitle(), u.getPolId(), u.getPushID(), u.getPushChannel(),
                        u.getName(), u.getUnit(), u.getGroup(), u.getImei(), u.getTime(), u.getGeoId()
                }
        );
    }

	public GeotableItem getLastGeotable() {
		SQLiteDatabase db = helper.getWritableDatabase();
        Cursor c = null;
        GeotableItem u = null;

        if (db != null) {
            c = db.rawQuery("select * from " +
                    GeotableDBHelper.GEO_TABLE, null);
            if (c != null) {
                while (c.moveToLast()) {
                    u = fillGeotable(c);
                }
                c.close();
            }
            db.close();
        }

		return u;
	}

	public void delGeotable(GeotableItem u) {
		SQLiteDatabase db = helper.getWritableDatabase();
        if (db != null) {
            db.execSQL("delete from user where " +
                        GeotableDBHelper.GEO_ID +
                        "=?",
                    new Object[] { u.getGeoId() }
            );
            db.close();
        }
	}

	public void delete() {
		SQLiteDatabase db = helper.getWritableDatabase();
        if (db != null) {
            db.execSQL("delete from " +
                    GeotableDBHelper.GEO_TABLE);
            db.close();
        }

	}

    public void LOG(String msg) {
        LOGD(this.getClass(), msg);
    }

}
